import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.metadata.data.ImageData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.youka.Application;
import com.youka.bussiness.common.utils.CollectionUtils;
import com.youka.bussiness.common.utils.DateUtil;
import com.youka.bussiness.common.utils.JsonUtil;
import com.youka.dao.*;
import com.youka.dao.entity.*;
import com.youka.service.SysRoomPriceService;
import com.youka.service.UserRentingService;
import com.youka.service.req.UserRentingReqDto;
import com.youka.service.yunding.WpNewPush;
import com.youka.service.yunding.YundingUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URL;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @author liulw
 * @Title:
 * @Package
 * @Description:
 * @date 2021/8/811:30 下午
 */
@RunWith(value = SpringRunner.class)
@SpringBootTest(classes = {Application.class})
@Slf4j
public class Cell {


    @Resource
    private SysRoomMapper sysRoomMapper;

    @Resource
    private SysRoomDistrictMapper sysRoomDistrictMapper;

    @Resource
    private SysRoomPriceService sysRoomPriceService;

    @Resource
    private UserInfoMapper userInfoMapper;

    @Resource
    private UserRentingMapper userRentingMapper;

    @Resource
    private SysRoomPriceMapper sysRoomPriceMapper;

    @Resource
    private UserRentingService userRentingService;

    @Resource
    private WpNewPush wpNewPush;

    private static String check(String date, boolean flag) {
        String item = date.replaceAll("/", "-");
        String[] items = item.split("-");
        String end = "";
        int i = 0;
        for (String str : items) {
            if (i == 0) {
                end = end.concat(str).concat("-");
            } else if (i == 1) {
                if (Integer.parseInt(str) < 10) {
                    end = end.concat("0").concat(str).concat("-");
                } else {
                    end = end.concat(str).concat("-");
                }
            } else {
                if (!flag) {
                    if (Integer.parseInt(str) < 10) {
                        end = end.concat("0").concat(str);
                    } else {
                        end = end.concat(str);
                    }
                } else {
                    String[] fl = str.split(" ");
                    if (Integer.parseInt(fl[0]) < 10) {
                        end = end.concat("0").concat(fl[0]).concat(" ").concat(fl[1]);
                    } else {
                        end = end.concat(str);
                    }
                }
            }
            i++;
        }
        return end;
    }

    /**
     * 读取excel内容
     * <p>
     * 用户模式下：
     * 弊端：对于少量的数据可以，单数对于大量的数据，会造成内存占据过大，有时候会造成内存溢出
     * 建议修改成事件模式
     */
    public static List<Map<String, String>> redExcel(String filePath) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        ArrayList<Map<String, String>> list = new ArrayList<>();

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            LinkedHashMap<String, String> map = new LinkedHashMap<>();
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);
                // cell.setCellType(XSSFCell.CELL_TYPE_STRING); 过期，使用下面替换
                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                map.put(getString(titleCell), getString(cell));
            }
            if (map.isEmpty()) {
                continue;
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 同步门锁信息，门锁密码，电表信息，电表度数和状态
     */
    @Test
    public void test() {
        List<SysRoom> sysRooms = sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>()
                .eq(SysRoom::getHousingId, 155));
//        List<JSONObject> jsonObjects = YundingUtil.searchHomeInfo();
//            JSONArray objects = YundingUtil.getHomeInfo("202304242900136825370","R202304250401425645132");
//        List<SysRoom> sysRooms = sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>()
//                .eq(SysRoom::getId, "R202304250401425645132"));
        int i = 0;
        for (SysRoom sysRoom : sysRooms) {
            i++;
            //门锁密码id
//            List<JSONObject> maps = YundingUtil.fetchPasswords(sysRoom.getHomeId(), sysRoom.getId(), "2");
//            if (!org.springframework.util.CollectionUtils.isEmpty(maps)) {
//                for (JSONObject object : maps) {
//                    sysRoom.setPasswordId(object.getString("id"));
//                }
//            }
            //门锁信息
            Map map1 = YundingUtil.getLockInfo(sysRoom.getHomeId(), sysRoom.getId());
            if (map1 != null) {
                sysRoom.setDoorLockElectric(map1.get("power").toString());
                sysRoom.setDoorLockOnline(Integer.parseInt(map1.get("onoff_line").toString()));
            }
            //电表信息
            Map<String, Object> map = YundingUtil.getElemeterInfo(sysRoom.getHomeId(), sysRoom.getId());
            if (map != null) {
                String enable_state = map.get("enable_state").toString();
                sysRoom.setElectricId(map.get("uuid").toString());
                sysRoom.setElectricMeterEnable(Integer.parseInt(enable_state));
                sysRoom.setElectricNum(new BigDecimal(map.get("consume_amount").toString()));
            }
            sysRoomMapper.updateById(sysRoom);
        }
        System.out.println(i);
    }

    @Test
    public void pay1() throws Exception {
        List<SysRoom> sysRooms = sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>().in(SysRoom::getHousingId, Arrays.asList(109, 103, 110)));
        List<SysRoom> sysRoomList = sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>().eq(SysRoom::getHousingId, 155));
        Collections.shuffle(sysRooms);
        int i = 0;
        for (SysRoom sysRoom : sysRoomList) {
            sysRoom.setThumb(sysRooms.get(i).getThumb());
            sysRoom.setImages(sysRooms.get(i).getImages());
            i++;
            sysRoomMapper.updateById(sysRoom);
        }
        System.out.println();
    }

    @Test
    public void pay() throws Exception {
        UserRentingReqDto dto = new UserRentingReqDto();
        dto.setOrderId("202302232001638265202");
        dto.setUserId(2757);
        userRentingService.payNotify(dto);

    }

    @Test
    public void addRoom() {
        List<JSONObject> objects = YundingUtil.searchHomeInfo();
        for (int i = 0; i < objects.size(); i++) {
            JSONObject jsonObject = objects.get(i);
            //房源下面的房间列表
            String homeId = jsonObject.getString("home_id");
            JSONArray room = jsonObject.getJSONArray("rooms");
            //房源下面的房间对应的设备号
            JSONArray devices = jsonObject.getJSONArray("devices");

            List<Map> list = JSONObject.parseArray(devices.toJSONString(), Map.class);

            for (int j = 0; j < room.size(); j++) {
                JSONObject json = room.getJSONObject(j);
                if (StringUtils.equals(json.getString("room_id"), homeId)) {
                    continue;
                } else {
                    SysRoom sysRoom = new SysRoom();
                    sysRoom.setSort("0");
                    sysRoom.setCreTime(LocalDateTime.now());
//                    sysRoom.setStatus(Const.ONE);
                    sysRoom.setId(json.getString("room_id"));
//                    sysRoom.setRoomType(sysRoomDistrict.getType());
                    sysRoom.setRoomNumber(json.getString("description"));


                    for (int k = 0; k < devices.size(); k++) {
                        if (CollectionUtils.isEmpty(devices)) {
                            continue;
                        }
                        JSONObject de = devices.getJSONObject(k);

                        if (!StringUtils.equals(de.getString("room_id"), sysRoom.getId())) {
                            if (StringUtils.equals(de.getString("type"), "gateway")) {
                                if (StringUtils.equals(de.getString("onoff"), "2")) {
                                    System.out.println();
                                    System.out.println(de.getString("room_id").concat("网关异常"));
                                    continue;
                                }
                            } else {
                                continue;
                            }
                            ;
                        } else {
                            //门锁
                            if (StringUtils.equals(de.getString("type"), "lock")) {
                                sysRoom.setLockId(de.getString("uuid"));
                                sysRoom.setDoorLockOnline(de.getIntValue("onoff"));
//                                sysRoom.setDoorLockElectric(de.getString("power"));
                            } else if (StringUtils.equals(de.getString("type"), "elemeter")) {
                                sysRoom.setElectricMeterEnable(de.getIntValue("onoff"));
                                sysRoom.setElectricId(de.getString("uuid"));
//                                sysRoom.setElectricNum(new BigDecimal(de.getFloat("amount")));
                            } else if (StringUtils.equals(de.getString("type"), "gateway")) {
                                if (StringUtils.equals(de.getString("onoff"), "2")) {
                                    System.out.println();
                                    System.out.println(de.getString("room_id").concat("网关异常"));
                                }
                            }
                        }
                    }

//                    sysRoom.setName(json.getString("room_name"));
//                    sysRoom.setHomeId(homeId);
//                    sysRoom.setHousingId(sysRoomDistrict.getId());
//                    sysRoom.setHomeName(sysRoomDistrict.getTitle());
//                    sysRoom.setHousingName(sysRoomDistrict.getTitle());
//                    sysRoom.setLatitude(sysRoomDistrict.getLatitude());
//                    sysRoom.setLongitude(sysRoomDistrict.getLongitude());
//                    sysRoom.setHomeName(sysRoomDistrict.getTitle());
//                    sysRoomMapper.insert(sysRoom);
//
//                    SysRoomPriceReqDto roomPriceReqDto = new SysRoomPriceReqDto();
//                    roomPriceReqDto.setIsDiscount(Const.ONE);
//                    roomPriceReqDto.setName(sysRoom.getName());
//                    roomPriceReqDto.setContent(String.valueOf(sysRoom.getId()));
//                    roomPriceReqDto.setIsRoom(Const.ZERO);
//                    sysRoomPriceService.insert(roomPriceReqDto);
                }
            }
        }
    }

    @Test
    public void selectdoor() {
        QueryWrapper<SysRoom> wrapper = new QueryWrapper<>();
        wrapper.lambda().eq(SysRoom::getHousingId, 115);
        wrapper.last("and password_id is null ");
        List<SysRoom> sysRooms = sysRoomMapper.selectList(wrapper);
//        for (SysRoom sysRoom : sysRooms) {
//            String pwdid = YundingUtil.fetchPasswords(sysRoom.getHomeId(), sysRoom.getId());
//            if (pwdid != null) {
//                sysRoom.setPasswordId(pwdid);//113 116
//                sysRoomMapper.updateById(sysRoom);
//            }
//
//        }
    }

    @Test
    public void sentMes() throws Exception {
        File file = new File("/Users/liulinwei/Desktop/测试房源.xlsx");
//        ExcelUtil excelUtil = ExcelUtil.getInstance();
//        List<Object> delixiGoodImportVos = excelUtil.readExcelByFile(file, UserRentingReqDto.class);
        List<Map<String, String>> maps = redExcel("/Users/liulinwei/Desktop/测试房源.xlsx");
        System.out.println(JsonUtil.objectToJson(maps));
        for (Map<String, String> map : maps) {
            UserRentingReqDto dto = new UserRentingReqDto();
//            UserInfo userInfos = userInfoMapper.selectOne(new LambdaQueryWrapper<UserInfo>().eq(UserInfo::getMobile, map.get("手机号")));
            dto.setPhone(map.get("手机号"));
//            dto.setStartRentTime(check(map.get("起租日期"), false));
            dto.setStartRentTime(map.get("起租日期"));

            dto.setQiyeId(1000000);
//            dto.setEndRentTime(check(map.get("租期截止时间"), false));
            dto.setEndRentTime(map.get("租期截止时间"));
            dto.setNumber(Integer.valueOf(map.get("入住人数")));
            SysRoom sysRoom = sysRoomMapper.selectOne(new LambdaQueryWrapper<SysRoom>()
                    .eq(SysRoom::getHousingName, map.get("项目名称"))
                    .eq(SysRoom::getRoomNumber, map.get("房号")));
            if (sysRoom == null) {
                System.out.println(map.get("项目名称") + map.get("房号") + "不存在");
            }
            dto.setRoomId(sysRoom.getId());
//            String item = check(map.get("下单时间"), true);
            String item = map.get("下单时间");
            try {
                LocalDateTime time = DateUtil.stringToLocalTime(item);
                dto.setCreTime(time);
            } catch (Exception e) {
                dto.setCreTime(LocalDateTime.now());
            }
            System.out.println(item);
            userRentingService.addRenting(dto);
        }

        //caYi1F6hudseDN41kF3216
//        sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>().eq(SysRoom::getHousingName,""))
//        userRentingService.addRenting();
//        wpPush.push1("2020202020202020", "2021-02-09 12:12:12", "oQqJt5RB89zZv5MrVwOyaUbJoWVc");
    }

    @Test
    public void sentMes1() throws Exception {
        File file = new File("/Users/liulinwei/Desktop/测试房源.xlsx");
//        ExcelUtil excelUtil = ExcelUtil.getInstance();
//        List<Object> delixiGoodImportVos = excelUtil.readExcelByFile(file, UserRentingReqDto.class);
        List<Map<String, String>> maps = redExcel("/Users/liulinwei/Desktop/云和客户导入.xlsx");
        System.out.println(JsonUtil.objectToJson(maps));
        for (Map<String, String> map : maps) {
            UserRentingReqDto userRentingdto = new UserRentingReqDto();
            UserRenting userRenting = userRentingMapper.selectOne(new LambdaQueryWrapper<UserRenting>()
                    .eq(UserRenting::getPhone, map.get("手机号")).last("limit 1"));
            if (userRenting != null) {
                if (StringUtils.isBlank(userRenting.getIdentityName())) {
                    userRenting.setIdentityName(map.get("租户"));
                }
                userRentingMapper.updateById(userRenting);
            }
        }

        //caYi1F6hudseDN41kF3216
//        sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>().eq(SysRoom::getHousingName,""))
//        userRentingService.addRenting();
//        wpPush.push1("2020202020202020", "2021-02-09 12:12:12", "oQqJt5RB89zZv5MrVwOyaUbJoWVc");
    }

    /**
     * 修改价格
     *
     * @throws Exception
     */
    @Test
    public void price() throws Exception {
        List<Map<String, String>> maps = redExcel("/Users/liulinwei/Desktop/住家租金价格.xlsx");
        //5b4423da6eaeea2ba2303f12 --银泰一店
        // 5c3d4c89db51b61a2b9f291c 皮革城电
        //5c3d4d31db51b61a2b9f2997 二店
        System.out.println(JsonUtil.objectToJson(maps));
        for (Map<String, String> hash : maps) {
            SysRoom sysRoom = sysRoomMapper.selectOne(new LambdaQueryWrapper<SysRoom>()
                    .eq(SysRoom::getHomeId, hash.get("房源"))
//                     .eq(SysRoom::getHomeId, "5c3d4d31db51b61a2b9f2997")
                    .eq(SysRoom::getRoomNumber, hash.get("房号")));
//            sysRoom.setPrice(new BigDecimal(hash.get("单月租赁价格（月）")));
            sysRoom.setDownStrokePrice(new BigDecimal(hash.get("起租价")));

//            SysRoomPrice sysRoomPrice = sysRoomPriceMapper.selectOne(new LambdaQueryWrapper<SysRoomPrice>()
//                    .eq(SysRoomPrice::getContent, sysRoom.getId())
//                    .eq(SysRoomPrice::getIsRoom, Const.ZERO)
//                    .eq(SysRoomPrice::getIsDiscount, Const.ONE)
//                    .eq(SysRoomPrice::getStatus, Const.ONE));
//            sysRoomPrice.setMonthlyRentMonthly(sysRoom.getPrice());
//            sysRoomPrice.setQuarterlyRentMonthly(sysRoom.getPrice());
//            sysRoomPrice.setQuarterlyRentQuarterly(sysRoom.getPrice().multiply(new BigDecimal("3")));
//            sysRoomPrice.setHalfyearRentMonthly(sysRoom.getPrice());
//            sysRoomPrice.setHalfyearRentQuarterly(sysRoom.getPrice().multiply(new BigDecimal("3")));
//            sysRoomPrice.setHalfyearRentHalfyear(sysRoom.getPrice().multiply(new BigDecimal("6")));
//            sysRoomPrice.setYearRentMonthly(sysRoom.getPrice());
//            sysRoomPrice.setYearRentQuarterly(sysRoom.getPrice().multiply(new BigDecimal("3")));
//            sysRoomPrice.setYearRentHalfyear(sysRoom.getPrice().multiply(new BigDecimal("6")));
//            sysRoomPrice.setYearRentYear(sysRoom.getPrice().multiply(new BigDecimal("12")));

            sysRoomMapper.updateById(sysRoom);
//            sysRoomPriceMapper.updateById(sysRoomPrice);

            System.out.println(sysRoom.getName());
        }
    }

    @Test
    public void push() {

        wpNewPush.push10("刘林威", "5sdfadf44", "2023-01-02", "oQqJt5b7LV43y6S51lN5qYLCYeyM");
    }

    @Test
    public void push2323() {
        List<SysRoom> sysRooms = sysRoomMapper.selectList(new LambdaQueryWrapper<SysRoom>().eq(SysRoom::getIsDoor, "1").like(SysRoom::getName, "云和"));
        for (int i = 0; i < sysRooms.size(); i++) {
            SysRoom sysRoom = sysRooms.get(i);
            if (!StringUtils.equals(sysRoom.getStatus(), "5") && "1".equals(sysRoom.getIsDoor())) {
                try {
                    Map<String, Object> map = YundingUtil.getLockInfo(sysRoom.getHomeId(), sysRoom.getId());
                    sysRoom.setDoorLockOnline((Integer) map.get("onoff_line"));
                    sysRoom.setDoorLockElectric(String.valueOf(map.get("power")));
                } catch (Exception e) {
//                    sysRoom.setDoorLockOnline(2);
//                    sysRoom.setDoorLockElectric("0");
//                    sysRoom.setIsDoor("0");
                }
                sysRoomMapper.updateById(sysRoom);
            }
        }
    }

    /**
     * 测试easyexcel读excel
     */
    @Test
    public void exceltest() {
        String fileName = Cell.class.getResource("/").getPath() + "demo" + File.separator + "demo.xlsx";

        //headRowNumber(1) 行头指定第几行，默认第一行。

        //方法二：手动写读取监听器 ，这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ReadData.class, new ReadDataListener()).sheet().doRead();

        //方法一：JDK8+ ,不用额外写一个DemoDataListener
        EasyExcel.read(fileName, ReadData.class, new PageReadListener<ReadData>(dataList -> {
            for (ReadData demoData : dataList) {
                log.info("读取到一条数据{}", JSON.toJSONString(demoData));
            }
        })).sheet().headRowNumber(2).doRead();

        // 写法4
        // 一个文件一个reader，读取多个shell
        try (ExcelReader excelReader = EasyExcel.read(fileName, ReadData.class, new ReadDataListener()).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        }
    }

    /**
     * 文件上传
     */
//    @PostMapping("upload")
//    @ResponseBody
   /* @Test
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), ReadData.class, new ReadDataListener()).sheet().doRead();
        return "success";
    }*/
    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
//    @GetMapping("downloadFailedUsingJson")
    /*@Test
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
//            EasyExcel.write(response.getOutputStream(), DownloadData.class).autoCloseStream(Boolean.FALSE).sheet("模板")
//                    .doWrite(data());
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }*/

    /**
     * 最简单的填充excel
     */
    @Test
    public void simpleFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String templateFileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + "easyFill.xlsx";

        // 方案1 根据对象填充
        String fileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        FillData fillData = new FillData();
        fillData.setName("张三");
        fillData.setNumber(5.2);
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);

        // 方案2 根据Map填充
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
//        Map<String, Object> map = MapUtils.newHashMap();
//        map.put("name", "张三");
//        map.put("number", 5.2);
//        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
    }

    /**
     * 填充列表
     */
    @Test
    public void listFill() {
        String templateFileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + "listFill.xlsx";
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
        // 如果填充list的对象是map,必须包涵所有list的key,哪怕数据为null，必须使用map.put(key,null)

        // 方案1 一下子全部放到内存里面 并填充
        List<FillData> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("张三");
            fillData.setNumber(1 + i);
            data.add(fillData);
        }
        String fileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
//        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data);

        // 方案2 分多次 填充 会使用文件缓存（省内存）多次需要吧数据给切好
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(data, writeSheet);
            excelWriter.fill(data, writeSheet);
        }
    }

    /**
     * 复杂的填充
     */
    @Test
    public void complexFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        String templateFileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + "complexFill.xlsx";
        String fileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + System.currentTimeMillis() + ".xlsx";
        // 方案1 分批写进去
        List<FillData> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("张三");
            fillData.setNumber(1 + i);
            data.add(fillData);
        }
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(data, fillConfig, writeSheet);
            excelWriter.fill(data, fillConfig, writeSheet);
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("date", "2019年10月9日13:28:28");
            map.put("total", 1000);
            excelWriter.fill(map, writeSheet);
        }
    }

    /**
     * 横向的填充
     */
    @Test
    public void horizontalFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        String templateFileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + "horizontalFill.xlsx";
        String fileName = Cell.class.getResource("/").getPath() + "fill" + File.separator + System.currentTimeMillis() + ".xlsx";
        // 方案1
        List<FillData> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("张三");
            fillData.setNumber(1 + i);
            data.add(fillData);
        }
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(data, fillConfig, writeSheet);
            excelWriter.fill(data, fillConfig, writeSheet);

            Map<String, Object> map = new HashMap<>();
            map.put("date", "2019年10月9日13:28:28");
            excelWriter.fill(map, writeSheet);
        }
    }

    private List<WriteData> data() {
        List<WriteData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            WriteData data = new WriteData();
            data.setString("字符串" + i);
            data.setDate(DateUtil.getCurrentDate());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    /**
     * 最简单的写
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName = Cell.class.getResource("/").getPath() + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, WriteData.class)
                .sheet("模板")
                .doWrite(() -> {
                    // 分页查询数据
                    return data();
                });

        // 写法2
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, WriteData.class).sheet("模板").doWrite(data());

        // 写法3
        // 这里 需要指定写用哪个class去写
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, WriteData.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        }
    }

    /**
     * 复杂头写入
     * <p>1. 创建excel对应的实体对象 参照{@link ComplexHeadData}
     * <p>3. 直接写即可
     */
    @Test
    public void complexHeadWrite() {
        String fileName = Cell.class.getResource("/").getPath() + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data());
    }

    /**
     * 图片导出
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link ImageDemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void imageWrite() throws Exception {
        String fileName = Cell.class.getResource("/").getPath() + System.currentTimeMillis() + ".xlsx";

        // 这里注意下 所有的图片都会放到内存 暂时没有很好的解法，大量图片的情况下建议 2选1:
        // 1. 这边的图片是测试用的，后续用正式地址
        String imagePath = Cell.class.getResource("/").getPath() + "converter" + File.separator + "img.jpg";
        try (InputStream inputStream = FileUtils.openInputStream(new File(imagePath))) {
            List<ImageDemoData> list = ListUtils.newArrayList();
            ImageDemoData imageDemoData = new ImageDemoData();
            list.add(imageDemoData);
            //todo 放入五种类型的图片 实际使用只要选一种即可
            imageDemoData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
            imageDemoData.setFile(new File(imagePath));
            imageDemoData.setString(imagePath);
            imageDemoData.setInputStream(inputStream);
            imageDemoData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));

            // 这里演示
            // 需要额外放入文字
            // 而且需要放入2个图片
            // 第一个图片靠左
            // 第二个靠右 而且要额外的占用他后面的单元格
            WriteCellData<Void> writeCellData = new WriteCellData<>();
            imageDemoData.setWriteCellDataFile(writeCellData);
            // 这里可以设置为 EMPTY 则代表不需要其他数据了
            writeCellData.setType(CellDataTypeEnum.STRING);
            writeCellData.setStringValue("额外的放一些文字");

            // 可以放入多个图片
            List<ImageData> imageDataList = new ArrayList<>();
            ImageData imageData = new ImageData();
            imageDataList.add(imageData);
            writeCellData.setImageDataList(imageDataList);
            // 放入2进制图片
            imageData.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
            // 图片类型
            imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
            // 上 右 下 左 需要留空
            // 这个类似于 css 的 margin
            // 这里实测 不能设置太大 超过单元格原始大小后 打开会提示修复。暂时未找到很好的解法。
            imageData.setTop(5);
            imageData.setRight(40);
            imageData.setBottom(5);
            imageData.setLeft(5);

            // 放入第二个图片
            imageData = new ImageData();
            imageDataList.add(imageData);
            writeCellData.setImageDataList(imageDataList);
            imageData.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
            imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
            imageData.setTop(5);
            imageData.setRight(5);
            imageData.setBottom(5);
            imageData.setLeft(50);
            // 设置图片的位置 假设 现在目标 是 覆盖 当前单元格 和当前单元格右边的单元格
            // 起点相对于当前单元格为0 当然可以不写
            imageData.setRelativeFirstRowIndex(0);
            imageData.setRelativeFirstColumnIndex(0);
            imageData.setRelativeLastRowIndex(0);
            // 前面3个可以不写  下面这个需要写 也就是 结尾 需要相对当前单元格 往右移动一格
            // 也就是说 这个图片会覆盖当前单元格和 后面的那一格
            imageData.setRelativeLastColumnIndex(1);

            // 写入数据
            EasyExcel.write(fileName, ImageDemoData.class).sheet().doWrite(list);
        }
    }
}
